package com.droidclaimsurveyor.dal;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.widget.Toast;

import com.droidclaimsurveyor.model.SurveyorModel;

public class SurveyorDal implements DAL <SurveyorModel, String>{
	public static String GetAllSurveyorId = "GetAllSurveyor";
	public static String GetLastSurveyorId = "GetLastSurveyor";
	public static String GetSurveyorByRowId = "GetSurveyorByRow";
	
	private SurveyorDBHelper dbHelper;
	private SQLiteDatabase db;
	private Context context;

	public SurveyorDal(Context context) {
		this.context = context;
		dbHelper = new SurveyorDBHelper(context,
				SurveyorDBHelper.DATABASE_NAME, null,
				SurveyorDBHelper.DATABASE_VERSION);
	}
	
	public List<SurveyorModel> getAll(String taskId,String... parameters){
		List<SurveyorModel> result = null;
		if(taskId.equals(SurveyorDal.GetAllSurveyorId)){
			result = getAllSurveyor(parameters);
		}else if(taskId.equals(SurveyorDal.GetSurveyorByRowId)){
			result = getAllSurveyorByRowId(parameters);
		}else if(taskId.equals(SurveyorDal.GetLastSurveyorId)){
			result = getLastSurveyor(parameters);
		}
		
		return result;
	}
	
	public List<SurveyorModel> getAllSurveyor(String... surveyorIds){
		
		List<SurveyorModel> result = new ArrayList<SurveyorModel>();
		try{
			String surveyorId = surveyorIds[0];
			Cursor cursor = getCursorBySurveyorId(surveyorId);
			if (cursor.moveToFirst()) {
				do {
					String dbSurveyorId = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_SURVEYOR_ID));
					if(surveyorId.equals(dbSurveyorId)){
						SurveyorModel model = new SurveyorModel();
						
						long rowId = cursor.getLong(cursor.getColumnIndex(SurveyorDBHelper.KEY_ID));
						long id = cursor.getLong(cursor.getColumnIndex(SurveyorDBHelper.KEY_DB_SERVER_ID));
						String surveyorid = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_SURVEYOR_ID));
						String name = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_NAME));
						String surname = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_SURNAME));
						String mobile = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_MOBILE));
						String email = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_EMAIL));
						String imgPath = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_IMG_PATH));
						boolean status = cursor.getInt(cursor.getColumnIndex(SurveyorDBHelper.KEY_STATUS))==1? true:false;
						
						model.setRowId(rowId);
						model.setEmail(email);
						model.setId(id);
						model.setImgPath(imgPath);
						model.setMobile(mobile);
						model.setName(name);
						model.setStatus(status);
						model.setSurname(surname);
						model.setSurveyorid(surveyorid);
						
						result.add(model);
					}
				} while (cursor.moveToNext());
			}		
		}catch(Exception ex){
		}
		
		return result;
	}

	public List<SurveyorModel> getAllSurveyorByRowId(String... rowIdStr){
		
		List<SurveyorModel> result = new ArrayList<SurveyorModel>();
		try{
			String rowId = rowIdStr[0];
			Cursor cursor = getCursorByRowId(rowId);
			if (cursor.moveToFirst()) {
				do {
					long dbRowId = cursor.getLong(cursor.getColumnIndex(SurveyorDBHelper.KEY_ID));
					if(rowIdStr.equals(Long.toString(dbRowId))){
						SurveyorModel model = new SurveyorModel();
						
						long id = cursor.getLong(cursor.getColumnIndex(SurveyorDBHelper.KEY_DB_SERVER_ID));
						String surveyorid = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_SURVEYOR_ID));
						String name = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_NAME));
						String surname = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_SURNAME));
						String mobile = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_MOBILE));
						String email = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_EMAIL));
						String imgPath = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_IMG_PATH));
						boolean status = cursor.getInt(cursor.getColumnIndex(SurveyorDBHelper.KEY_STATUS))==1? true:false;
						
						model.setRowId(dbRowId);
						model.setEmail(email);
						model.setId(id);
						model.setImgPath(imgPath);
						model.setMobile(mobile);
						model.setName(name);
						model.setStatus(status);
						model.setSurname(surname);
						model.setSurveyorid(surveyorid);
						
						result.add(model);
					}
				} while (cursor.moveToNext());
			}		
		}catch(Exception ex){
		}
		
		return result;
	}
	
	public List<SurveyorModel> getLastSurveyor(String... surveyorIds){
		
		List<SurveyorModel> result = new ArrayList<SurveyorModel>();
		try{
			String surveyorId = surveyorIds[0];
			Cursor cursor = getLastRecord(surveyorId);
			if (cursor.moveToFirst()) {
				do {
					String dbSurveyorId = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_SURVEYOR_ID));
					if(surveyorId.equals(dbSurveyorId)){
						SurveyorModel model = new SurveyorModel();
						
						long rowId = cursor.getLong(cursor.getColumnIndex(SurveyorDBHelper.KEY_ID));
						long id = cursor.getLong(cursor.getColumnIndex(SurveyorDBHelper.KEY_DB_SERVER_ID));
						String surveyorid = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_SURVEYOR_ID));
						String name = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_NAME));
						String surname = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_SURNAME));
						String mobile = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_MOBILE));
						String email = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_EMAIL));
						String imgPath = cursor.getString(cursor.getColumnIndex(SurveyorDBHelper.KEY_IMG_PATH));
						boolean status = cursor.getInt(cursor.getColumnIndex(SurveyorDBHelper.KEY_STATUS))==1? true:false;
						
						model.setRowId(rowId);
						model.setEmail(email);
						model.setId(id);
						model.setImgPath(imgPath);
						model.setMobile(mobile);
						model.setName(name);
						model.setStatus(status);
						model.setSurname(surname);
						model.setSurveyorid(surveyorid);
						
						result.add(model);
					}
				} while (cursor.moveToNext());
			}		
		}catch(Exception ex){
		}
		
		return result;
	}	
	
	public void close() {
		dbHelper.close();
	}

	public void open() {
		db = null;
		try {
			db = dbHelper.getWritableDatabase();
		} catch (Exception ex) {
			db = dbHelper.getReadableDatabase();
		}
	}

	public long add(SurveyorModel surveyor) {
		long rowIndex = -1;

		try {			
			ContentValues data = new ContentValues();
			
			data.put(SurveyorDBHelper.KEY_DB_SERVER_ID, surveyor.getId());
			data.put(SurveyorDBHelper.KEY_SURVEYOR_ID, surveyor.getSurveyorid());
			data.put(SurveyorDBHelper.KEY_NAME,surveyor.getName());
			data.put(SurveyorDBHelper.KEY_SURNAME,surveyor.getSurname());
			data.put(SurveyorDBHelper.KEY_MOBILE,surveyor.getMobile());
			data.put(SurveyorDBHelper.KEY_EMAIL,surveyor.getEmail());
			data.put(SurveyorDBHelper.KEY_IMG_PATH, surveyor.getImgPath());
			data.put(SurveyorDBHelper.KEY_STATUS,surveyor.isStatus());

			// return row id to caller
			rowIndex = db.insert(SurveyorDBHelper.DATABASE_TABLE, null, data);
		} catch (Exception ex) {
			Toast.makeText(context, ex.getMessage(), Toast.LENGTH_SHORT).show();
		}

		return rowIndex;
	}

	public boolean update(long rowIndex, SurveyorModel surveyor) {		
		ContentValues newValue = new ContentValues();
		
		newValue.put(SurveyorDBHelper.KEY_DB_SERVER_ID, surveyor.getId());
		newValue.put(SurveyorDBHelper.KEY_SURVEYOR_ID, surveyor.getSurveyorid());
		newValue.put(SurveyorDBHelper.KEY_NAME,surveyor.getName());
		newValue.put(SurveyorDBHelper.KEY_SURNAME,surveyor.getSurname());
		newValue.put(SurveyorDBHelper.KEY_MOBILE,surveyor.getMobile());
		newValue.put(SurveyorDBHelper.KEY_EMAIL,surveyor.getEmail());
		newValue.put(SurveyorDBHelper.KEY_IMG_PATH, surveyor.getImgPath());
		newValue.put(SurveyorDBHelper.KEY_STATUS,surveyor.isStatus());
		
		return db.update(SurveyorDBHelper.DATABASE_TABLE, newValue, SurveyorDBHelper.KEY_ID + "=" + rowIndex,
				null) > 0;
	}

	public boolean remove(long index) {
		return db.delete(SurveyorDBHelper.DATABASE_TABLE,
				SurveyorDBHelper.KEY_ID + "=" + index, null) > 0;
	}

	public Cursor getAllCursor(String orderBy) {
		String columnName[] = new String[] { SurveyorDBHelper.KEY_ID,
				SurveyorDBHelper.KEY_DB_SERVER_ID,
				SurveyorDBHelper.KEY_SURVEYOR_ID,
				SurveyorDBHelper.KEY_NAME,
				SurveyorDBHelper.KEY_SURNAME,
				SurveyorDBHelper.KEY_MOBILE,
				SurveyorDBHelper.KEY_EMAIL,
				SurveyorDBHelper.KEY_IMG_PATH,
				SurveyorDBHelper.KEY_STATUS
				};

		return db.query(SurveyorDBHelper.DATABASE_TABLE, columnName, null,
				null, null, null, orderBy);
	}
	
	public Cursor getCursorByServeyorDBId(String dbId){
		String columnName[] = new String[] { SurveyorDBHelper.KEY_ID,
				SurveyorDBHelper.KEY_DB_SERVER_ID,
				SurveyorDBHelper.KEY_SURVEYOR_ID,
				SurveyorDBHelper.KEY_NAME,
				SurveyorDBHelper.KEY_SURNAME,
				SurveyorDBHelper.KEY_MOBILE,
				SurveyorDBHelper.KEY_EMAIL,
				SurveyorDBHelper.KEY_IMG_PATH,
				SurveyorDBHelper.KEY_STATUS
				};
		
		String selection = SurveyorDBHelper.KEY_DB_SERVER_ID + " = ?";
		String[] selectionArgs = new String[] {dbId};
		return db.query(SurveyorDBHelper.DATABASE_TABLE, columnName, selection,selectionArgs, null, null, null);
	}

	public Cursor getCursorBySurveyorId(String surveyorIdStr){
		String columnName[] = new String[] { SurveyorDBHelper.KEY_ID,
				SurveyorDBHelper.KEY_DB_SERVER_ID,
				SurveyorDBHelper.KEY_SURVEYOR_ID,
				SurveyorDBHelper.KEY_NAME,
				SurveyorDBHelper.KEY_SURNAME,
				SurveyorDBHelper.KEY_MOBILE,
				SurveyorDBHelper.KEY_EMAIL,
				SurveyorDBHelper.KEY_IMG_PATH,
				SurveyorDBHelper.KEY_STATUS
			};
		
		String selection = SurveyorDBHelper.KEY_SURVEYOR_ID + " = ?";
		String[] selectionArgs = new String[] {surveyorIdStr};
		return db.query(SurveyorDBHelper.DATABASE_TABLE, columnName, selection,selectionArgs, null, null, null);
	}
	
	public Cursor getCursorByRowId(String rowIdStr){
		String columnName[] = new String[] { SurveyorDBHelper.KEY_ID,
				SurveyorDBHelper.KEY_DB_SERVER_ID,
				SurveyorDBHelper.KEY_SURVEYOR_ID,
				SurveyorDBHelper.KEY_NAME,
				SurveyorDBHelper.KEY_SURNAME,
				SurveyorDBHelper.KEY_MOBILE,
				SurveyorDBHelper.KEY_EMAIL,
				SurveyorDBHelper.KEY_IMG_PATH,
				SurveyorDBHelper.KEY_STATUS
		};
		
		String selection = SurveyorDBHelper.KEY_ID + " = ?";
		String[] selectionArgs = new String[] {rowIdStr};
		return db.query(SurveyorDBHelper.DATABASE_TABLE, columnName, selection,selectionArgs, null, null, null);
	}
	
	public Cursor getLastRecord(String surveyorId){
		String sql = "SELECT * FROM ? where ?=? ORDER BY ? DESC LIMIT 1";
		String[] selectionArgs = new String[] {
				SurveyorDBHelper.DATABASE_TABLE,
				SurveyorDBHelper.KEY_SURVEYOR_ID,
				surveyorId,
				SurveyorDBHelper.KEY_ID
				};
		return db.rawQuery(sql,selectionArgs);
	}
}
